import ExcelJS from 'exceljs';
import { resources } from './sheet.config';
import { getMergedRegions, getRangeFromExcelLetter } from '@/utils/excel';
export default class ResolveExcel {
  file: any;
  locale: string = 'zh-CN';
  appVersion: string = '0.7.0';
  woorkbook: any;
  sheets: any[] = [];
  valueTypeMapping: any = {
    3: 1, // 文本,
    2: 2, // 数字,
  };
  constructor(file: any) {
    this.file = file;
  }

  async readExcelFile() {
    // 创建新的工作簿
    const workbook: any = new ExcelJS.Workbook();
    const buffer = await this.file.arrayBuffer();
    // 根据文件名获取Sheet 加载 Excel 文件
    const pending = workbook[this.getFileExtension(this.file.name) as any]?.load(buffer);
    if (pending) {
      await pending;
    } else {
      throw new Error('Unsupported file type');
    }
    // (window as any)._workbook = workbook;
    this.woorkbook = workbook;
    this.sheets = workbook.worksheets.sort((a: any, b: any) => a.orderNo - b.orderNo);
    console.log('workbook-', workbook);
    return this.handleToUniverjs(workbook, this.file);
  }

  getFileExtension(fileName: string) {
    return fileName.split('.').pop();
  }
  getFileName(fileName: string) {
    return fileName.split('.').slice(0, -1).join('.');
  }
  handleToUniverjs(workbook: any, file: File) {
    const univerWorkbook: any = {
      locale: this.locale,
      appVersion: this.appVersion,
      id: file.name,
      name: this.getFileName(file.name),
      resources: resources(),
    };
    const sheetOrder: any[] = [];
    const sheets: any = {};
    const cellStyleMap: any = {};
    const validationObj: any = {};
    const SHEET_FILTER_PLUGIN: any = {};
    this.sheets.forEach((sheet) => {
      // this.handleToUniverjsSheet(sheet);
      const actualSheet: any = this.handleToUniverjsSheet(sheet, cellStyleMap);
      sheets[actualSheet.id] = actualSheet;
      const freeze = this.handleFreezen(sheet);
      validationObj[actualSheet.id] = this.handleDataValidate(sheet.dataValidations.model);
      if (freeze) {
        actualSheet.freeze = freeze;
      }
      if (sheet.autoFilter) {
        const range = getRangeFromExcelLetter(sheet.autoFilter);
        SHEET_FILTER_PLUGIN[actualSheet.id] = {
          cachedFilteredOut: [],
          filterColumns: [],
          ref: { ...range, rangeType: 2 },
        };
      }
      sheetOrder.push(actualSheet.id);
    });
    const styles: any = {};
    for (const key in cellStyleMap) {
      styles[cellStyleMap[key]] = JSON.parse(key);
    }
    console.log('validationObj-', validationObj);
    univerWorkbook.resources[13].data = JSON.stringify(validationObj);
    univerWorkbook.resources[14].data = JSON.stringify(SHEET_FILTER_PLUGIN);
    univerWorkbook.sheets = sheets;
    univerWorkbook.sheetOrder = sheetOrder;
    univerWorkbook.styles = styles;
    return univerWorkbook;
  }

  handleToUniverjsSheet(sheet: any, cellStyleMap: any = {}) {
    sheet.__id = this.fallbackUUID();
    const columnData: any[] = [];
    sheet._columns.forEach((column: any) => {
      columnData.push(this.handleToUniverjsColumn(column));
    });
    const rowData: any[] = [];
    sheet._rows.forEach((row: any) => {
      rowData.push(this.handleToUniverjsRow(row));
    });
    const cellData: any[][] = [];
    sheet._rows.forEach((row: any) => {
      const rowCells: any[] = [];
      for (let i = 0; i < row._cells.length; i++) {
        const cell = row._cells[i];
        const univerCell: any = !cell ? {} : this.handleToUniverjsCell(cell);
        const style = this.handleCellStyle(cell, cellStyleMap);
        if (style) {
          univerCell.s = style;
        }
        rowCells.push(univerCell);
      }
      cellData.push(rowCells);
    });
    const mergeData = this.handleMergeData(sheet);
    const initConfig = {
      id: sheet.__id,
      name: sheet.name,
      defaultColumnWidth: 88,
      defaultRowHeight: 24,
      freeze: { xSplit: 0, ySplit: 0, startRow: -1, startColumn: -1 },
      rowHeader: { width: 46, hidden: 0 },
      columnHeader: { height: 20, hidden: 0 },
      hidden: sheet.state == 'hidden' ? 1 : 0,
      mergeData,
      rowCount: 1000,
      columnCount: 20,
      rightToLeft: 0,
      scrollLeft: 0,
      scrollTop: 0,
      showGridlines: 1,
      tabColor: '',
      zoomRatio: 1,
      columnData,
      rowData,
      cellData,
    };

    return initConfig;
  }
  handleToUniverjsColumn(column: any) {
    // 宽度系数
    const widthCoefficient = 10;
    const ret: any = {};
    if (column.width) {
      ret.w = column.width * widthCoefficient;
    }
    return ret;
  }
  handleToUniverjsRow(row: any) {
    // 高度系数
    const heightCoefficient = 1.4;
    const ret: any = {
      ah: 24,
      ia: 0,
    };
    if (row.height) {
      ret.h = row.height * heightCoefficient;
    }
    return ret;
  }
  handleToUniverjsCell(cell: any) {
    if (cell.model.master) {
      return {};
    }
    if (typeof cell.value === 'object') {
      if (cell?.value?.formula) {
        return {
          f: '=' + cell.value.formula.replace('_xlfn._xlws.', ''),
          v: cell.value.result,
          t: 2,
        };
      }
      if (cell?.value?.richText && Array.isArray(cell?.value?.richText)) {
        return {
          t: 1,
          v: cell.value.richText.reduce((a: any, b: any) => {
            return a + b.text;
          }, ''),
        };
      }
    }
    return {
      v: cell.value,
      t: this.valueTypeMapping[cell.type] || 1,
    };
  }
  handleMergeData(sheet: any) {
    if (JSON.stringify(sheet._merges) === '{}') {
      return [];
    }
    const mergeData: any[] = [];
    for (const key in sheet._merges) {
      const merge = sheet._merges[key];
      mergeData.push({
        startRow: merge.top - 1,
        startColumn: merge.left - 1,
        endRow: merge.bottom - 1,
        endColumn: merge.right - 1,
        sheetId: sheet.__id,
        unitId: this.getFileName(this.file.name),
      });
    }
    return mergeData;
  }
  handleCellStyle(cell: any, styleMap: any) {
    if (!cell) {
      return null;
    }
    const originStyle = cell.style;
    if (!originStyle || JSON.stringify(originStyle) === '{}') {
      return null;
    }
    // 边框处理
    let bd: any = null;
    const directionMap: any = {
      top: 't',
      bottom: 'b',
      left: 'l',
      right: 'r',
    };
    for (const key in originStyle.border) {
      if (!bd) {
        bd = {};
      }
      bd[directionMap[key]] = {
        cl: {
          rgb:
            (originStyle.border[key].color?.argb?.substr(2)
              ? `#${originStyle.border[key].color?.argb?.substr(2)}`.toLowerCase()
              : null) || '#000000',
        },
        s: 1,
      };
    }
    let ht = undefined;
    let vt = undefined;
    let tb = undefined;
    if (originStyle.alignment) {
      // 水平对齐
      const hMap: any = {
        left: 1,
        center: 2,
        right: 3,
      };
      ht = hMap[originStyle.alignment.horizontal as any] || 1;
      // 垂直对齐
      const vMap: any = {
        top: 1,
        middle: 2,
        bottom: 3,
      };
      vt = vMap[originStyle.alignment.vertical as any] || 1;
      // 换行
      tb = originStyle.alignment.wrapText ? 3 : undefined;
    }

    // 填充
    let bg = {
      rgb: '',
    };
    if (
      originStyle.fill &&
      originStyle.fill.type === 'pattern' &&
      originStyle.fill.pattern !== 'none'
    ) {
      bg = {
        rgb:
          (originStyle.fill.fgColor?.argb?.substr(2)
            ? `#${originStyle.fill.fgColor?.argb?.substr(2)}`.toLowerCase()
            : null) || '',
      };
    }

    // 字体颜色
    let cl = {
      rgb: '#000000',
    };
    let bl = undefined;
    let it = undefined;
    let st = undefined;
    let ul = undefined;
    if (originStyle.font) {
      bl = originStyle.font.bold ? 1 : 0;
      if (originStyle.font.color?.argb) {
        cl = {
          rgb: '#' + originStyle.font.color.argb.substr(2).toLowerCase(),
        };
      }
      // 斜体
      it = originStyle.font.italic ? 1 : 0;
      // 删除线
      st = originStyle.font.strike
        ? {
            cl,
            s: 1,
          }
        : null;
      // 下划线
      ul = originStyle.font.underline
        ? {
            cl,
            s: 1,
          }
        : null;
    }
    // 是否加粗

    const tempStyle: any = {
      bd,
      bl,
      ht,
      vt,
      bg,
      cl,
      it,
      st,
      ul,
      tb,
    };
    const style: any = {};
    for (const key in tempStyle) {
      if (tempStyle[key]) {
        style[key] = tempStyle[key];
      }
    }
    const key = JSON.stringify(style);
    if (styleMap[key]) {
      return styleMap[key];
    }
    styleMap[key] = this.fallbackUUID();
    return styleMap[key];
  }
  handleFreezen(sheet: any) {
    const freeze: any = sheet.views.filter((v: any) => v.state === 'frozen')[0];
    if (!freeze) {
      return null;
    }
    return {
      startRow: freeze.ySplit,
      startColumn: freeze.xSplit,
      xSplit: freeze.xSplit,
      ySplit: freeze.ySplit,
    };
  }
  handleDataValidate(model: any) {
    const validateConfig: any = [];
    if (!model || JSON.stringify(model) === '{}') {
      return validateConfig;
    }
    // 处理下拉框
    const handleList = (model: any) => {
      const formualMap: any = {};
      for (const key in model) {
        const validation = model[key];
        const { formulae, type, operator } = validation;
        if (type === 'list') {
          const curFormual = formulae[0];
          if (!formualMap[curFormual]) {
            formualMap[curFormual] = {
              address: [],
              formula1: /^"(.+,?)+"$/.test(curFormual)
                ? curFormual.replaceAll('"', '')
                : `=${curFormual}`,
              formula2: '',
              uid: this.fallbackUUID(),
              errorStyle: 1,
              renderMode: 1,
              type,
            };
          }
          formualMap[curFormual].address.push(key);
        } else if (formulae && type !== 'time') {
          const curFormual = type + ',' + operator + ',' + formulae.join(',');
          if (!formualMap[curFormual]) {
            const param: any = {
              address: [],
              uid: this.fallbackUUID(),
              errorStyle: 1,
              renderMode: 1,
              operator,
              type,
            };
            if (type === 'date') {
              param.formula1 = `${formulae[0].getFullYear()}/${formulae[0].getMonth() + 1}/${formulae[0].getDate()}`;
              if (formulae.length === 2) {
                param.formula2 = `${formulae[1].getFullYear()}/${formulae[1].getMonth() + 1}/${formulae[1].getDate()}`;
              }
            } else {
              param.formula1 = `${formulae[0]}`;
              if (formulae.length === 2) {
                param.formula2 = `${formulae[1]}`;
              }
            }

            formualMap[curFormual] = param;
          }
          formualMap[curFormual].address.push(key);
        }
      }
      console.log('formualMap-', formualMap);
      for (const key in formualMap) {
        const formual = formualMap[key];
        const validation = getMergedRegions(formual.address);
        formual.ranges = validation;
        delete formual.address;
        validateConfig.push(formual);
      }
    };
    handleList(model);
    return validateConfig;
  }
  private fallbackUUID() {
    return 'xxxxxxxxxxxx'.replace(/[xy]/g, function (c) {
      const r = (Math.random() * 16) | 0,
        v = c === 'x' ? r : (r & 0x3) | 0x8;
      return v.toString(16);
    });
  }
}
